#学员在线管理系统(mysql版)
import pymysql

class Stu:
    def __init__(self):
        self.db = pymysql.connect(host = "localhost",user = "root",password = "",db = "studb",charset="utf8")
        self.cursor = self.db.cursor()
    def findAll(self):
        sql = "select id,name,age,classid from stu "
        try:
            self.cursor.execute(sql)
            stulist = self.cursor.fetchall()
            self.db.commit()
            return stulist
        except Exception as err:
            self.db.rollback()
            print("SQL执行错误！原因：",err)
    def dele(self,id):
        sql = "delete from stu where id=%d"%(id)
        try:
            self.cursor.execute(sql)
            self.db.commit()
        except Exception as err:
            self.db.rollback()
            print("SQL删除数据错误！原因：",err)
    def insert(self,data):
        sql = "insert into stu(name,age,classid) values('%s','%d','%s')"%(data)
        try:
            self.cursor.execute(sql) 
            self.db.commit()          
        except Exception as err:
            self.db.rollback()
            print("SQL增加数据错误！原因：",err)        
    def __del__(self):
        self.db.close()

s = Stu()
stulist = s.findAll()


#定义个学生信息的输出函数
def showStu(stulist):
    stulist = s.findAll()
    if len(stulist) == 0:
        print("============没有学员信息可以输出！==============")
        return
    print("|{0:<5}|{1:<10}|{2:<5}|{3:<10}".format("sid","name","age","classid"))
    print("-"*40)
    for  x in stulist:
        print("|{0:<5}|{1:<10}|{2:<5}|{3:<10}".format(x[0],x[1],x[2],x[3]))
   
   

while True:
    #输出初始界面
    print("="*12,"学员管理系统","="*14)
    print("{0:1} {1:13} {2:15}".format(" ","1. 查看学员信息","2. 添加学员信息"))
    print("{0:1} {1:13} {2:15}".format(" ","3. 删除学员信息","4. 退出系统"))
    print("="*40)
    key = input("请输入对应的选择：")
    #根据key，判断并执行对应的操作
    if key == '1':
        print("="*12,"学员信息浏览","="*14)
        showStu(stulist)
        input("按回车键继续......")
    elif key == '2':
        print("="*12,"学员信息添加","="*14)
        stu = {}
        name = input("请输入要添加的姓名：")
        age = int(input("请输入要添加的年龄："))
        classid = input("请输入要添加的班级号：")
        stu = (name,age,classid)
        print(stu)
        s.insert(stu)
        showStu(stulist)
        input("按回车键继续......")
    elif key == '3':
        print("="*12,"学员信息删除","="*14)
        showStu(stulist)
        sid = eval(input("请输入你要删除的信息id号："))
        s.dele(sid)
        showStu(stulist)
        input("按回车键继续......")
    elif key == '4':
        print("="*12,"再见","="*14)
        break
    else:
        print("======== 无效的键盘输入！ ==========")


